--reporte de tiempo en solucionar casos atendidos por tecnicos - (poner opcion para ver detalles especificos de todos los casos atendidos por los tecnicos)
--seran tomados en cuenta nicamente los casos finalizados, debido a que se deben hacer calculos de suma y resta de tiempos, y no se pueden hacer sobre casos que aun no han finalizado legalmente 

--parte 1 - vr1
/*select
      a.`c8_tecnico_id`,
      concat(a.`c8_tecnico_id`,' - ',b.`v64_usuario_nombre`,' ',b.`v64_usuario_apellido1`,' ',b.`v64_usuario_apellido2`,' - ',f.`v128_rol_nombre`) as tecnico,
      a.`i10_caso_id` as casos_especifico,
      g.`v64_problema_subespecifico`,
      g.`i4_dias_solucion`*24+g.`i4_horas_solucion`+g.`i4_minutos_solucion`/60 as horas_solucion
from
    tm_casos a
    left join tm_usuarios b
    on a.`c8_tecnico_id`=b.`c8_usuario_id`
    left join tm_roles f
    on b.`i8_rol_id`=f.`i8_rol_id`
    left join te_problema_subespecifico g
    on a.`i8_problema_subespecifico_id` = g.`i8_problema_subespecifico_id`
where
     a.`c8_tecnico_id`!=""
     and
     a.`i2_estado_caso_id` not in(1,2,5,7)
order by
      a.`c8_tecnico_id`
      */


--VERSION 2

select
      DISTINCT
      a.`c8_tecnico_id`,
      concat(a.`c8_tecnico_id`,' - ',b.`v64_usuario_nombre`,' ',b.`v64_usuario_apellido1`,' ',b.`v64_usuario_apellido2`,' - ',f.`v128_rol_nombre`) as tecnico,
      a.`i10_caso_id` as casos_especifico,
      i.`v32_estado_caso` as estado,
      g.`v64_problema_subespecifico`,
      g.`i4_dias_solucion`*24+g.`i4_horas_solucion`+g.`i4_minutos_solucion`/60 as horas_solucion,
      a.`dt_fecha_reportada`,
      a.`dt_fecha_asignada`,
      a.`dt_fecha_finalizacion`,
      datediff(a.`dt_fecha_asignada`,a.`dt_fecha_reportada`) as dias_reportado,
      if(j.`dt_fecha_fin`=j.`dt_fecha_inicio`,timediff(j.`hm_hora_fin`,j.`hm_hora_inicio`),timediff(j.`hm_hora_fin`,8)) as horas_reportado,
      datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`) as dias_asignado,
      if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8)) as horas_asignado,
      datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`) as dias_rem_externa,
      if(m.`dt_fecha_fin`=m.`dt_fecha_inicio`,timediff(m.`hm_hora_fin`,m.`hm_hora_inicio`),timediff(m.`hm_hora_fin`,8)) as horas_rem_externa,
      datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`) as dias_reabierto,
      if(n.`dt_fecha_fin`=n.`dt_fecha_inicio`,timediff(n.`hm_hora_fin`,n.`hm_hora_inicio`),timediff(n.`hm_hora_fin`,8)) as horas_reabierto,
      datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`) as dias_rem_interna,
      if(o.`dt_fecha_fin`=o.`dt_fecha_inicio`,timediff(o.`hm_hora_fin`,o.`hm_hora_inicio`),timediff(o.`hm_hora_fin`,8)) as horas_rem_interna,
      datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`) as dias_pausa,
      if(p.`dt_fecha_fin`=p.`dt_fecha_inicio`,timediff(p.`hm_hora_fin`,p.`hm_hora_inicio`),timediff(p.`hm_hora_fin`,8)) as horas_pausa,
      (
      if(isnull(datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`)),0,datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`))
       -
      if(isnull(datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`)),0,datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`))
       -
      if(isnull(datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`)),0,datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`))
       -
      if(isnull(datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`)),0,datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`))
       -
      if(isnull(datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`)),0,datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`))
      )
      as consolidado_dias,
      (
      if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8))
      )
     
      as consolidado_horas
      
from
    tm_casos a
    left join tm_usuarios b
    on a.`c8_tecnico_id`=b.`c8_usuario_id`
    left join tm_roles f
    on b.`i8_rol_id`=f.`i8_rol_id`
    left join te_problema_subespecifico g
    on a.`i8_problema_subespecifico_id` = g.`i8_problema_subespecifico_id`
    left join tm_estado_caso i
    on a.`i2_estado_caso_id`=i.`i2_estado_caso_id`
    left join te_casos_estado_caso_tiempos j
    on a.`i10_caso_id`=j.`i10_caso_id` and j.`i2_estado_caso_id`='1'
    left join te_casos_estado_caso_tiempos k
    on a.`i10_caso_id`=k.`i10_caso_id` and k.`i2_estado_caso_id`='2'
    left join te_casos_estado_caso_tiempos l
    on a.`i10_caso_id`=l.`i10_caso_id` and a.`i2_estado_caso_id`='3'
    left join te_casos_estado_caso_tiempos m
    on a.`i10_caso_id`=m.`i10_caso_id` and m.`i2_estado_caso_id`='4'
    left join te_casos_estado_caso_tiempos n
    on a.`i10_caso_id`=n.`i10_caso_id` and n.`i2_estado_caso_id`='5'
    left join te_casos_estado_caso_tiempos o
    on a.`i10_caso_id`=o.`i10_caso_id` and o.`i2_estado_caso_id`='6'
    left join te_casos_estado_caso_tiempos p
    on a.`i10_caso_id`=p.`i10_caso_id` and p.`i2_estado_caso_id`='7'
where
     a.`c8_tecnico_id`!=""
     and
     a.`i2_estado_caso_id` = 3
order by
      a.`c8_tecnico_id`,a.`i10_caso_id`


-- VERSION 2.1


--casos con medicion de tiempo - VERSION 2
select
      DISTINCT
      a.`c8_tecnico_id`,
      concat(a.`c8_tecnico_id`,' - ',b.`v64_usuario_nombre`,' ',b.`v64_usuario_apellido1`,' ',b.`v64_usuario_apellido2`,' - ',f.`v128_rol_nombre`) as tecnico,
      a.`i10_caso_id` as casos_especifico,
      i.`v32_estado_caso` as estado,
      g.`v64_problema_subespecifico`,
      g.`i4_dias_solucion` as dias_estimados_solucion,
      concat(g.`i4_horas_solucion`,':',g.`i4_minutos_solucion`,':00') as horas_estimada_solucion,
      /*a.`dt_fecha_reportada`,
      a.`dt_fecha_asignada`,
      a.`dt_fecha_finalizacion`,
      datediff(a.`dt_fecha_asignada`,a.`dt_fecha_reportada`) as dias_reportado,
      if(j.`dt_fecha_fin`=j.`dt_fecha_inicio`,timediff(j.`hm_hora_fin`,j.`hm_hora_inicio`),timediff(j.`hm_hora_fin`,8)) as horas_reportado,
      datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`) as dias_asignado,
      if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8)) as horas_asignado,
      datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`) as dias_rem_externa,
      if(m.`dt_fecha_fin`=m.`dt_fecha_inicio`,timediff(m.`hm_hora_fin`,m.`hm_hora_inicio`),timediff(m.`hm_hora_fin`,8)) as horas_rem_externa,
      datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`) as dias_reabierto,
      if(n.`dt_fecha_fin`=n.`dt_fecha_inicio`,timediff(n.`hm_hora_fin`,n.`hm_hora_inicio`),timediff(n.`hm_hora_fin`,8)) as horas_reabierto,
      datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`) as dias_rem_interna,
      if(o.`dt_fecha_fin`=o.`dt_fecha_inicio`,timediff(o.`hm_hora_fin`,o.`hm_hora_inicio`),timediff(o.`hm_hora_fin`,8)) as horas_rem_interna,
      datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`) as dias_pausa,
      if(p.`dt_fecha_fin`=p.`dt_fecha_inicio`,timediff(p.`hm_hora_fin`,p.`hm_hora_inicio`),timediff(p.`hm_hora_fin`,8)) as horas_pausa,*/
      (
      if(isnull(datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`)),0,datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`))
       -
      if(isnull(datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`)),0,datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`))
       -
      if(isnull(datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`)),0,datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`))
       -
      if(isnull(datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`)),0,datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`))
       -
      if(isnull(datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`)),0,datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`))
      )
      as consolidado_dias,
      (
      if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8))
      )
     
      as consolidado_horas
      
from
    tm_casos a
    left join tm_usuarios b
    on a.`c8_tecnico_id`=b.`c8_usuario_id`
    left join tm_roles f
    on b.`i8_rol_id`=f.`i8_rol_id`
    left join te_problema_subespecifico g
    on a.`i8_problema_subespecifico_id` = g.`i8_problema_subespecifico_id`
    left join tm_estado_caso i
    on a.`i2_estado_caso_id`=i.`i2_estado_caso_id`
    left join te_casos_estado_caso_tiempos j
    on a.`i10_caso_id`=j.`i10_caso_id` and j.`i2_estado_caso_id`='1'
    left join te_casos_estado_caso_tiempos k
    on a.`i10_caso_id`=k.`i10_caso_id` and k.`i2_estado_caso_id`='2'
    left join te_casos_estado_caso_tiempos l
    on a.`i10_caso_id`=l.`i10_caso_id` and a.`i2_estado_caso_id`='3'
    left join te_casos_estado_caso_tiempos m
    on a.`i10_caso_id`=m.`i10_caso_id` and m.`i2_estado_caso_id`='4'
    left join te_casos_estado_caso_tiempos n
    on a.`i10_caso_id`=n.`i10_caso_id` and n.`i2_estado_caso_id`='5'
    left join te_casos_estado_caso_tiempos o
    on a.`i10_caso_id`=o.`i10_caso_id` and o.`i2_estado_caso_id`='6'
    left join te_casos_estado_caso_tiempos p
    on a.`i10_caso_id`=p.`i10_caso_id` and p.`i2_estado_caso_id`='7'
where
     a.`c8_tecnico_id`!=""
     and
     a.`i2_estado_caso_id` = 3
order by
      a.`c8_tecnico_id`,a.`i10_caso_id`


-- VERSION 2.4
--casos con medicion de tiempo -
select
      a.`c8_tecnico_id`,
      concat(a.`c8_tecnico_id`,' - ',b.`v64_usuario_nombre`,' ',b.`v64_usuario_apellido1`,' ',b.`v64_usuario_apellido2`,' - ',f.`v128_rol_nombre`) as tecnico,
      a.`i10_caso_id` as casos_especifico,
      i.`v32_estado_caso` as estado,
      g.`v64_problema_subespecifico`,
      sum(g.`i4_dias_solucion`) as dias_estimados_solucion,
      sum((g.`i4_horas_solucion`+ g.`i4_minutos_solucion`/60)) as horas_estimada_solucion,
      /*a.`dt_fecha_reportada`,
      a.`dt_fecha_asignada`,
      a.`dt_fecha_finalizacion`,
      datediff(a.`dt_fecha_asignada`,a.`dt_fecha_reportada`) as dias_reportado,
      if(j.`dt_fecha_fin`=j.`dt_fecha_inicio`,timediff(j.`hm_hora_fin`,j.`hm_hora_inicio`),timediff(j.`hm_hora_fin`,8)) as horas_reportado,
      datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`) as dias_asignado,
      if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8)) as horas_asignado,
      datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`) as dias_rem_externa,
      if(m.`dt_fecha_fin`=m.`dt_fecha_inicio`,timediff(m.`hm_hora_fin`,m.`hm_hora_inicio`),timediff(m.`hm_hora_fin`,8)) as horas_rem_externa,
      datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`) as dias_reabierto,
      if(n.`dt_fecha_fin`=n.`dt_fecha_inicio`,timediff(n.`hm_hora_fin`,n.`hm_hora_inicio`),timediff(n.`hm_hora_fin`,8)) as horas_reabierto,
      datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`) as dias_rem_interna,
      if(o.`dt_fecha_fin`=o.`dt_fecha_inicio`,timediff(o.`hm_hora_fin`,o.`hm_hora_inicio`),timediff(o.`hm_hora_fin`,8)) as horas_rem_interna,
      datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`) as dias_pausa,
      if(p.`dt_fecha_fin`=p.`dt_fecha_inicio`,timediff(p.`hm_hora_fin`,p.`hm_hora_inicio`),timediff(p.`hm_hora_fin`,8)) as horas_pausa,*/
      sum(
      if(isnull(datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`)),0,datediff(a.`dt_fecha_finalizacion`,a.`dt_fecha_asignada`))
       -
      if(isnull(datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`)),0,datediff(m.`dt_fecha_fin`,m.`dt_fecha_inicio`))
       -
      if(isnull(datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`)),0,datediff(n.`dt_fecha_fin`,n.`dt_fecha_inicio`))
       -
      if(isnull(datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`)),0,datediff(o.`dt_fecha_fin`,o.`dt_fecha_inicio`))
       -
      if(isnull(datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`)),0,datediff(p.`dt_fecha_fin`,p.`dt_fecha_inicio`))
      )
      as consolidado_dias,
      sum(
      HOUR(if(isnull(k.`dt_fecha_fin`)=false,if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8)),'00:00:00'))
      +
      MINUTE(if(isnull(k.`dt_fecha_fin`)=false,if(k.`dt_fecha_fin`=k.`dt_fecha_inicio`,timediff(k.`hm_hora_fin`,k.`hm_hora_inicio`),timediff(k.`hm_hora_fin`,8)),'00:00:00'))/60
      )
     
      as consolidado_horas
      
from
    tm_casos a
    left join tm_usuarios b
    on a.`c8_tecnico_id`=b.`c8_usuario_id`
    left join tm_roles f
    on b.`i8_rol_id`=f.`i8_rol_id`
    left join te_problema_subespecifico g
    on a.`i8_problema_subespecifico_id` = g.`i8_problema_subespecifico_id`
    left join tm_estado_caso i
    on a.`i2_estado_caso_id`=i.`i2_estado_caso_id`
    left join te_casos_estado_caso_tiempos j
    on a.`i10_caso_id`=j.`i10_caso_id` and j.`i2_estado_caso_id`='1'
    left join te_casos_estado_caso_tiempos k
    on a.`i10_caso_id`=k.`i10_caso_id` and k.`i2_estado_caso_id`='2'
    left join te_casos_estado_caso_tiempos l
    on a.`i10_caso_id`=l.`i10_caso_id` and a.`i2_estado_caso_id`='3'
    left join te_casos_estado_caso_tiempos m
    on a.`i10_caso_id`=m.`i10_caso_id` and m.`i2_estado_caso_id`='4'
    left join te_casos_estado_caso_tiempos n
    on a.`i10_caso_id`=n.`i10_caso_id` and n.`i2_estado_caso_id`='5'
    left join te_casos_estado_caso_tiempos o
    on a.`i10_caso_id`=o.`i10_caso_id` and o.`i2_estado_caso_id`='6'
    left join te_casos_estado_caso_tiempos p
    on a.`i10_caso_id`=p.`i10_caso_id` and p.`i2_estado_caso_id`='7'
where
     a.`c8_tecnico_id`!=""
     and
     a.`i2_estado_caso_id` = 3
group by
      a.`c8_tecnico_id`
order by
      a.`c8_tecnico_id`,a.`i10_caso_id`


